﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Clinica_Frba.AbmdeProfesional
{
    public partial class Modificacion : Form
    {
        public Modificacion()
        {
            InitializeComponent();
        }
        int idp;
        public Modificacion(int id)
        {
            InitializeComponent();
            idp = id;
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            SqlCommand consulta = new SqlCommand("SELECT * FROM DUNDER_MIFFLIN.ESPECIALIDAD", myConnection);
            SqlDataReader da = consulta.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("id_esp");
            dt.Columns.Add("desc_esp");
            dt.Load(da);
            listBox1.DataSource = dt;
            listBox1.DisplayMember = "desc_esp";
            listBox1.ValueMember = "id_esp";
            listBox1.SelectedIndex = 0;
            SqlCommand llenar = new SqlCommand("SELECT * FROM DUNDER_MIFFLIN.PROFESIONAL WHERE ID_PROF=" + id, myConnection);
            SqlDataReader reader = llenar.ExecuteReader();
            reader.Read();
            if (reader.IsDBNull(2)) { }else{ textBox1.Text = reader.GetString(2); }
            if (reader.IsDBNull(3)) { } else { textBox2.Text = reader.GetString(3); }
            if (reader.IsDBNull(5)) { } else { textBox3.Text = reader.GetString(5); }
            if (reader.IsDBNull(6)) { } else { textBox4.Text = reader.GetString(6); }
            if (reader.IsDBNull(7)) { } else { textBox5.Text = reader.GetString(7); }
            if (reader.IsDBNull(8)) { } else { textBox10.Text = reader.GetString(8); }
            if (reader.IsDBNull(11)) { } else { textBox6.Text = reader.GetString(11); }
            if (reader.IsDBNull(9)) { } else { textBox7.Text = reader.GetDateTime(9).ToString(); }
            reader.Close();
            SqlCommand esp = new SqlCommand("SELECT DUNDER_MIFFLIN.ESPECIALIDAD.DESC_ESP FROM DUNDER_MIFFLIN.PROFESIONAL_ESPECIALIDAD LEFT JOIN DUNDER_MIFFLIN.ESPECIALIDAD ON DUNDER_MIFFLIN.PROFESIONAL_ESPECIALIDAD.ID_ESP=DUNDER_MIFFLIN.ESPECIALIDAD.ID_ESP WHERE DUNDER_MIFFLIN.PROFESIONAL_ESPECIALIDAD.ID_PROF=" + id, myConnection);
            SqlDataReader resp = esp.ExecuteReader();
            while(resp.Read()){
            listBox2.Items.Add(resp.GetString(0));}
            resp.Close();
            myConnection.Close();
        }
        private void Modificacion_Load(object sender, EventArgs e)
        {
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            string comando;
            comando = "SELECT * FROM DUNDER_MIFFLIN.ESPECIALIDAD";
            SqlCommand consulta = new SqlCommand(comando, myConnection);
            SqlDataReader da = consulta.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("id_esp");
            dt.Columns.Add("desc_esp");
            dt.Load(da);
            listBox1.DisplayMember = "desc_esp";
            listBox1.ValueMember = "id_esp";
            listBox1.DataSource = dt;
            listBox1.SelectedIndex = 0;
            myConnection.Close();
        }



        private void button3_Click(object sender, EventArgs e)
        {
            if (listBox1.Items.Count != 0)
            {
                bool ver = true;
                int i;

                for (i = 0; i < listBox2.Items.Count; i++)
                {
                    if (listBox2.GetItemText(listBox2.Items[i]) == listBox1.GetItemText(listBox1.SelectedItem)) { ver = false; }
                }
                if (ver)
                {

                    listBox2.Items.Add(listBox1.GetItemText(listBox1.SelectedItem));

                }
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (listBox2.Items.Count != 0)
            {
                listBox2.Items.Remove(listBox2.SelectedItem);
                if (listBox2.Items.Count > 0) { listBox2.SelectedIndex = 0; }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            SqlCommand habilitar = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROFESIONAL SET ESTADO='H' WHERE ID_PROF=" + idp, myConnection);
            habilitar.ExecuteNonQuery();            
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == "") { return; }
            if (textBox2.Text == "") { return; }
            if (textBox3.Text == "") { return; }
            if (textBox6.Text == "") { return; }
            if (listBox2.Items.Count == 0) { return; }
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            SqlCommand matric = new SqlCommand("SELECT * FROM DUNDER_MIFFLIN.PROFESIONAL WHERE MATRICULA='"+textBox6.Text+"'", myConnection);
            SqlDataReader ma = matric.ExecuteReader();
            if (ma.HasRows) { MessageBox.Show("Numero de matricula repetido"); return; }
            ma.Close();
            SqlCommand beg = new SqlCommand("BEGIN TRAN P", myConnection);
            beg.ExecuteNonQuery();
            int i,idfu;
            try
            {
                SqlCommand update = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROFESIONAL SET DIRECCION='" + textBox4.Text + "',TELEFONO='" + textBox5.Text + "',Mail='" + textBox10.Text + "',Matricula='" + textBox6.Text + "' WHERE ID_PROF=" + idp, myConnection);
                update.ExecuteNonQuery();
                SqlCommand del = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.PROFESIONAL_ESPECIALIDAD WHERE ID_PROF=" + idp, myConnection);
                del.ExecuteNonQuery();
                for (i = 0; i < listBox2.Items.Count; i++)
                {
                    SqlCommand idf = new SqlCommand("SELECT ID_ESP FROM DUNDER_MIFFLIN.ESPECIALIDAD WHERE DESC_ESP='" + listBox2.GetItemText(listBox2.Items[i]) + "'", myConnection);
                    SqlDataReader func = idf.ExecuteReader();
                    func.Read();
                    idfu = Convert.ToInt32(func.GetValue(0));
                    func.Close();
                    SqlCommand insert2 = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.PROFESIONAL_ESPECIALIDAD(ID_PROF,ID_ESP) VALUES(" + idp + "," + idfu + ")", myConnection);
                    insert2.ExecuteNonQuery();
                }
                SqlCommand c = new SqlCommand("COMMIT TRAN R", myConnection);
                c.ExecuteNonQuery();
                MessageBox.Show("Actualizacion ok", "Modificacion de profesional");
                this.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Modificacion de profesional");
                SqlCommand rb = new SqlCommand("ROLLBACK TRAN P", myConnection);
                rb.ExecuteNonQuery();
            }
            
        }
    }
}
